/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package east2west.dao;

import east2west.dto.CarDetailDTO;
import east2west.dto.CarModelDTO;
import east2west.dto.CarTypesDTO;
import east2west.dto.CarsDTO;
import east2west.interfaces.CarInterface;
import east2west.util.DatabaseUtil;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

/**
 *
 * @author anhtuan
 */
public class CarDAO implements CarInterface {

    Connection con = null;
    CarsDTO cars = null;
    CarModelDTO modelDTO = null;
    CarTypesDTO typeDTO = null;
    CarDetailDTO detailDTO = null;
    CallableStatement call = null;
    Statement st = null;
    PreparedStatement pst = null;
    ResultSet rs = null;
    private final String SQL_READ_BY_ALL = "select * from V_Cars";
    private final String SQL_READ_BY_ID = "select * from V_Cars where CarId = ?";
    private final String SQL_READ_BY_NAME = "select * from V_Cars where CarName = ?";
    private final String SQL_READ_BY_MODELID = "select * from V_Cars where ModelId = ?";
    private final String SQL_READ_BY_TYPEID = "select * from V_Cars where TypeId = ?";
    private final String SQL_READ_BY_AIRCONDITIONAL = "select * from V_Cars where AirConditional = ?";
    private final String SQL_READ_BY_QUANTITY = "select * from V_Cars where Quantity = ?";
    private final String SQL_READ_BY_PRICE = "select * from V_Cars where Price = ?";
    private final String SQL_READ_BY_SEATQTY = "select * from V_Cars where SeatQty = ?";
    private final String SQL_CREATE = "{call sp_Insert_Cars (?,?,?,?,?,?,?,?,?)}";
    private final String SQL_UPDATE = "{call sp_Update_Cars (?,?,?,?,?,?,?,?,?)}";
    private final String SQL_DELETE = "{call sp_Delete_Cars (?)}";

    public ArrayList<CarsDTO> readByAll() {
        ArrayList<CarsDTO> list = null;
        try {
            con = DatabaseUtil.getConnection();
            st = con.createStatement();
            rs = st.executeQuery(SQL_READ_BY_ALL);
            list = new ArrayList<CarsDTO>();
            while (rs.next()) {
                cars = new CarsDTO();
                //cardetail
                CarDetailDAO cardDetailDAO = new CarDetailDAO();
                //lay thong tin cua carDetail
                detailDTO = cardDetailDAO.getTop1ImageFromCarId(rs.getInt(1));
                cars.setDetailDTO(detailDTO);
                cars.setCarName(rs.getString(2));
                cars.setDetail(rs.getString(3));
                //modelDTO
                modelDTO = new CarModelDTO();
                modelDTO.setModelId(rs.getInt(4));
                modelDTO.setModelName(rs.getString(5));
                cars.setModelDTO(modelDTO);
                //typeDTO
                typeDTO = new CarTypesDTO();
                typeDTO.setTypeId(rs.getInt(6));
                typeDTO.setTypeName(rs.getString(7));
                cars.setTypeDTO(typeDTO);

                cars.setAirConditional(rs.getByte(8));
                cars.setQuantity(rs.getInt(9));
                cars.setSeatQty(rs.getInt(10));
                cars.setPrice(rs.getFloat(11));

                list.add(cars);
            }
            rs.close();
            st.close();
            return list;
        } catch (Exception ex) {
            return null;
        } finally {
            DatabaseUtil.closeConnection(con);
        }
    }

    public CarsDTO findByid(int id) {
        try {
            con = DatabaseUtil.getConnection();
            pst = con.prepareCall(SQL_READ_BY_ID);
            pst.setInt(1, id);
            rs = pst.executeQuery();
            while (rs.next()) {
                cars = new CarsDTO();
                //cardetail
                CarDetailDAO cardDetailDAO = new CarDetailDAO();
                //lay thong tin cua carDetail
                detailDTO = cardDetailDAO.getTop1ImageFromCarId(rs.getInt(1));
                cars.setDetailDTO(detailDTO);
                cars.setCarName(rs.getString(2));
                cars.setDetail(rs.getString(3));
                //modelDTO
                modelDTO = new CarModelDTO();
                modelDTO.setModelId(rs.getInt(4));
                modelDTO.setModelName(rs.getString(5));
                cars.setModelDTO(modelDTO);
                //typeDTO
                typeDTO = new CarTypesDTO();
                typeDTO.setTypeId(rs.getInt(6));
                typeDTO.setTypeName(rs.getString(7));
                cars.setTypeDTO(typeDTO);

                cars.setAirConditional(rs.getByte(8));
                cars.setQuantity(rs.getInt(9));
                cars.setSeatQty(rs.getInt(10));
                cars.setPrice(rs.getFloat(11));
            }
            rs.close();
            pst.close();
            return cars;
        } catch (Exception ex) {
            return null;
        } finally {
            DatabaseUtil.closeConnection(con);
        }
    }

    public ArrayList<CarsDTO> findByName(String name) {
        ArrayList<CarsDTO> list = null;
        try {
            con = DatabaseUtil.getConnection();
            pst = con.prepareCall(SQL_READ_BY_NAME);
            pst.setString(1, name);
            rs = pst.executeQuery();
            list = new ArrayList<CarsDTO>();
            while (rs.next()) {
                cars = new CarsDTO();
                cars.setCarId(rs.getInt(1));
                cars.setCarName(rs.getString(2));
                cars.setDetail(rs.getString(3));
                //modelDTO
                modelDTO = new CarModelDTO();
                modelDTO.setModelId(rs.getInt(4));
                modelDTO.setModelName(rs.getString(5));
                cars.setModelDTO(modelDTO);
                //typeDTO
                typeDTO = new CarTypesDTO();
                typeDTO.setTypeId(rs.getInt(6));
                typeDTO.setTypeName(rs.getString(7));
                cars.setTypeDTO(typeDTO);

                cars.setAirConditional(rs.getByte(8));
                cars.setQuantity(rs.getInt(9));
                cars.setSeatQty(rs.getInt(10));
                cars.setPrice(rs.getFloat(11));

                list.add(cars);
            }
            rs.close();
            pst.close();
            return list;
        } catch (Exception ex) {
            return null;
        } finally {
            DatabaseUtil.closeConnection(con);
        }
    }

    public ArrayList<CarsDTO> findByModeId(int modeId) {
        ArrayList<CarsDTO> list = null;
        try {
            con = DatabaseUtil.getConnection();
            pst = con.prepareStatement(SQL_READ_BY_MODELID);
            pst.setInt(1, modeId);
            rs = pst.executeQuery();
            list = new ArrayList<CarsDTO>();
            while (rs.next()) {
                cars = new CarsDTO();
                CarDetailDAO cardDetailDAO = new CarDetailDAO();
                //lay thong tin cua carDetail
                detailDTO = cardDetailDAO.getTop1ImageFromCarId(rs.getInt(1));
                cars.setDetailDTO(detailDTO);
                cars.setCarName(rs.getString(2));
                cars.setDetail(rs.getString(3));
                //modelDTO
                modelDTO = new CarModelDTO();
                modelDTO.setModelId(rs.getInt(4));
                modelDTO.setModelName(rs.getString(5));
                cars.setModelDTO(modelDTO);
                //typeDTO
                typeDTO = new CarTypesDTO();
                typeDTO.setTypeId(rs.getInt(6));
                typeDTO.setTypeName(rs.getString(7));
                cars.setTypeDTO(typeDTO);

                cars.setAirConditional(rs.getByte(8));
                cars.setQuantity(rs.getInt(9));
                cars.setSeatQty(rs.getInt(10));
                cars.setPrice(rs.getFloat(11));

                list.add(cars);
            }
            rs.close();
            pst.close();
            return list;
        } catch (Exception ex) {
            return null;
        } finally {
            DatabaseUtil.closeConnection(con);
        }
    }

    public ArrayList<CarsDTO> findByTypeId(int typeId) {
        ArrayList<CarsDTO> list = null;
        try {
            con = DatabaseUtil.getConnection();
            pst = con.prepareStatement(SQL_READ_BY_TYPEID);
            pst.setInt(1, typeId);
            rs = pst.executeQuery();
            list = new ArrayList<CarsDTO>();
            while (rs.next()) {
                cars = new CarsDTO();
                CarDetailDAO cardDetailDAO = new CarDetailDAO();
                //lay thong tin cua carDetail
                detailDTO = cardDetailDAO.getTop1ImageFromCarId(rs.getInt(1));
                cars.setDetailDTO(detailDTO);
                cars.setCarName(rs.getString(2));
                cars.setDetail(rs.getString(3));
                //modelDTO
                modelDTO = new CarModelDTO();
                modelDTO.setModelId(rs.getInt(4));
                modelDTO.setModelName(rs.getString(5));
                cars.setModelDTO(modelDTO);
                //typeDTO
                typeDTO = new CarTypesDTO();
                typeDTO.setTypeId(rs.getInt(6));
                typeDTO.setTypeName(rs.getString(7));
                cars.setTypeDTO(typeDTO);

                cars.setAirConditional(rs.getByte(8));
                cars.setQuantity(rs.getInt(9));
                cars.setSeatQty(rs.getInt(10));
                cars.setPrice(rs.getFloat(11));

                list.add(cars);
            }
            rs.close();
            pst.close();
            return list;
        } catch (Exception ex) {
            return null;
        } finally {
            DatabaseUtil.closeConnection(con);
        }
    }

    public ArrayList<CarsDTO> findByAirConditional(byte airConditional) {
        ArrayList<CarsDTO> list = null;
        try {
            con = DatabaseUtil.getConnection();
            pst = con.prepareStatement(SQL_READ_BY_AIRCONDITIONAL);
            pst.setByte(1, airConditional);
            rs = pst.executeQuery();
            list = new ArrayList<CarsDTO>();
            while (rs.next()) {
                cars = new CarsDTO();
                CarDetailDAO cardDetailDAO = new CarDetailDAO();
                //lay thong tin cua carDetail
                detailDTO = cardDetailDAO.getTop1ImageFromCarId(rs.getInt(1));
                cars.setDetailDTO(detailDTO);
                cars.setCarName(rs.getString(2));
                cars.setDetail(rs.getString(3));
                //modelDTO
                modelDTO = new CarModelDTO();
                modelDTO.setModelId(rs.getInt(4));
                modelDTO.setModelName(rs.getString(5));
                cars.setModelDTO(modelDTO);
                //typeDTO
                typeDTO = new CarTypesDTO();
                typeDTO.setTypeId(rs.getInt(6));
                typeDTO.setTypeName(rs.getString(7));
                cars.setTypeDTO(typeDTO);

                cars.setAirConditional(rs.getByte(8));
                cars.setQuantity(rs.getInt(9));
                cars.setSeatQty(rs.getInt(10));
                cars.setPrice(rs.getFloat(11));

                list.add(cars);
            }
            rs.close();
            pst.close();
            return list;
        } catch (Exception ex) {
            return null;
        } finally {
            DatabaseUtil.closeConnection(con);
        }
    }

    public ArrayList<CarsDTO> findByQuanTity(int qTy) {
        ArrayList<CarsDTO> list = null;
        try {
            con = DatabaseUtil.getConnection();
            pst = con.prepareStatement(SQL_READ_BY_QUANTITY);
            pst.setInt(1, qTy);
            rs = pst.executeQuery();
            list = new ArrayList<CarsDTO>();
            while (rs.next()) {
                cars = new CarsDTO();
                CarDetailDAO cardDetailDAO = new CarDetailDAO();
                //lay thong tin cua carDetail
                detailDTO = cardDetailDAO.getTop1ImageFromCarId(rs.getInt(1));
                cars.setDetailDTO(detailDTO);
                cars.setCarName(rs.getString(2));
                cars.setDetail(rs.getString(3));
                //modelDTO
                modelDTO = new CarModelDTO();
                modelDTO.setModelId(rs.getInt(4));
                modelDTO.setModelName(rs.getString(5));
                cars.setModelDTO(modelDTO);
                //typeDTO
                typeDTO = new CarTypesDTO();
                typeDTO.setTypeId(rs.getInt(6));
                typeDTO.setTypeName(rs.getString(7));
                cars.setTypeDTO(typeDTO);

                cars.setAirConditional(rs.getByte(8));
                cars.setQuantity(rs.getInt(9));
                cars.setSeatQty(rs.getInt(10));
                cars.setPrice(rs.getFloat(11));

                list.add(cars);
            }
            rs.close();
            pst.close();
            return list;
        } catch (Exception ex) {
            return null;
        } finally {
            DatabaseUtil.closeConnection(con);
        }
    }

    public ArrayList<CarsDTO> findByPrice(float price) {
        ArrayList<CarsDTO> list = null;
        try {
            con = DatabaseUtil.getConnection();
            pst = con.prepareStatement(SQL_READ_BY_PRICE);
            pst.setFloat(1, price);
            rs = pst.executeQuery();
            list = new ArrayList<CarsDTO>();
            while (rs.next()) {
                cars = new CarsDTO();
                CarDetailDAO cardDetailDAO = new CarDetailDAO();
                //lay thong tin cua carDetail
                detailDTO = cardDetailDAO.getTop1ImageFromCarId(rs.getInt(1));
                cars.setDetailDTO(detailDTO);
                cars.setCarName(rs.getString(2));
                cars.setDetail(rs.getString(3));
                //modelDTO
                modelDTO = new CarModelDTO();
                modelDTO.setModelId(rs.getInt(4));
                modelDTO.setModelName(rs.getString(5));
                cars.setModelDTO(modelDTO);
                //typeDTO
                typeDTO = new CarTypesDTO();
                typeDTO.setTypeId(rs.getInt(6));
                typeDTO.setTypeName(rs.getString(7));
                cars.setTypeDTO(typeDTO);

                cars.setAirConditional(rs.getByte(8));
                cars.setQuantity(rs.getInt(9));
                cars.setSeatQty(rs.getInt(10));
                cars.setPrice(rs.getFloat(11));

                list.add(cars);
            }
            rs.close();
            pst.close();
            return list;
        } catch (Exception ex) {
            return null;
        } finally {
            DatabaseUtil.closeConnection(con);
        }
    }

    public ArrayList<CarsDTO> findByDTO(CarsDTO dto) {
        ArrayList<CarsDTO> list = null;
        try {
            con = DatabaseUtil.getConnection();

            call = con.prepareCall("{call sp_Search_Car (?,?,?,?,?)}");

            call.setString(1, dto.getModelDTO().getModelName());
            call.setString(2, dto.getTypeDTO().getTypeName());
            call.setByte(3, dto.getAirConditional());
            call.setInt(4, dto.getSeatQty());
            call.setInt(5, dto.getSeatQtyMax());

            rs = call.executeQuery();
            list = new ArrayList<CarsDTO>();
            while (rs.next()) {
                cars = new CarsDTO();
                CarDetailDAO cardDetailDAO = new CarDetailDAO();
                //lay thong tin cua carDetail
                detailDTO = cardDetailDAO.getTop1ImageFromCarId(rs.getInt(1));
                cars.setDetailDTO(detailDTO);
                cars.setCarName(rs.getString(2));
                cars.setDetail(rs.getString(3));
                //modelDTO
                modelDTO = new CarModelDTO();
                modelDTO.setModelId(rs.getInt(4));
                modelDTO.setModelName(rs.getString(5));
                cars.setModelDTO(modelDTO);
                //typeDTO
                typeDTO = new CarTypesDTO();
                typeDTO.setTypeId(rs.getInt(6));
                typeDTO.setTypeName(rs.getString(7));
                cars.setTypeDTO(typeDTO);

                cars.setAirConditional(rs.getByte(8));
                cars.setQuantity(rs.getInt(9));
                cars.setSeatQty(rs.getInt(10));
                cars.setPrice(rs.getFloat(11));
                list.add(cars);
            }
            rs.close();
            call.close();
            return list;
        } catch (Exception ex) {
            return null;
        } finally {
            DatabaseUtil.closeConnection(con);
        }
    }

    public ArrayList<CarsDTO> readByTopCar(int top) {
        ArrayList<CarsDTO> list = null;
        try {
            con = DatabaseUtil.getConnection();
            pst = con.prepareStatement("select top (?) * from V_Cars");
            pst.setInt(1, top);
            //lay thong tin tat ca cac xe
            rs = pst.executeQuery();
            list = new ArrayList<CarsDTO>();
            while (rs.next()) {
                cars = new CarsDTO();
                //khai bao carDetailDAO
                CarDetailDAO cardDetailDAO = new CarDetailDAO();
                //lay thong tin cua carDetail
                detailDTO = cardDetailDAO.getTop1ImageFromCarId(rs.getInt(1));
                cars.setCarName(rs.getString(2));
                cars.setDetailDTO(detailDTO);
                list.add(cars);
            }
            rs.close();

            return list;
        } catch (Exception ex) {
            return null;
        } finally {
            DatabaseUtil.closeConnection(con);
        }
    }

    public ArrayList<CarsDTO> readByOtherCarId(int id) {
        ArrayList<CarsDTO> list = null;
        try {
            con = DatabaseUtil.getConnection();
            pst = con.prepareCall("select * from V_Cars where CarId != ?");
            pst.setInt(1, id);
            //lay thong tin tat ca cac xe
            rs = pst.executeQuery();

            list = new ArrayList<CarsDTO>();
            while (rs.next()) {
                cars = new CarsDTO();
                //khai bao carDetailDAO
                CarDetailDAO cardDetailDAO = new CarDetailDAO();
                //lay thong tin cua carDetail
                detailDTO = cardDetailDAO.getTop1ImageFromCarId(rs.getInt(1));
                cars.setCarName(rs.getString(2));

                cars.setDetailDTO(detailDTO);
                cars.setDetail(rs.getString(3));
                list.add(cars);
            }
            rs.close();

            return list;
        } catch (Exception ex) {
            return null;
        } finally {
            DatabaseUtil.closeConnection(con);
        }
    }

    public int create(CarsDTO dto) {
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall(SQL_CREATE);
            call.setString(1, dto.getCarName());
            call.setString(2, dto.getDetail());
            call.setInt(3, dto.getModelId());
            call.setInt(4, dto.getTypeId());
            if (dto.getAirConditional() == 1) {
                call.setBoolean(5, true);
            } else {
                call.setBoolean(5, false);
            }
            // call.setByte(5, dto.getAirConditional());
            call.setInt(6, dto.getQuantity());
            call.setInt(7, dto.getSeatQty());
            call.setFloat(8, dto.getPrice());
            call.registerOutParameter(9, java.sql.Types.INTEGER);
            call.executeUpdate();
            int carId = call.getInt(9);
            return carId;
        } catch (Exception ex) {
            return 0;
        } finally {
            try {
                DatabaseUtil.closeConnection(con);
                call.close();
            } catch (SQLException ex) {
            }
        }
    }

    public ArrayList<CarsDTO> findBySeatQty(int seatQty) {
        ArrayList<CarsDTO> list = null;
        try {
            con = DatabaseUtil.getConnection();
            pst = con.prepareStatement(SQL_READ_BY_SEATQTY);
            pst.setFloat(1, seatQty);
            rs = pst.executeQuery();
            list = new ArrayList<CarsDTO>();
            while (rs.next()) {
                cars = new CarsDTO();
                CarDetailDAO cardDetailDAO = new CarDetailDAO();
                //lay thong tin cua carDetail
                detailDTO = cardDetailDAO.getTop1ImageFromCarId(rs.getInt(1));
                cars.setDetailDTO(detailDTO);
                cars.setCarName(rs.getString(2));
                cars.setDetail(rs.getString(3));
                //modelDTO
                modelDTO = new CarModelDTO();
                modelDTO.setModelId(rs.getInt(4));
                modelDTO.setModelName(rs.getString(5));
                cars.setModelDTO(modelDTO);
                //typeDTO
                typeDTO = new CarTypesDTO();
                typeDTO.setTypeId(rs.getInt(6));
                typeDTO.setTypeName(rs.getString(7));
                cars.setTypeDTO(typeDTO);

                cars.setAirConditional(rs.getByte(8));
                cars.setQuantity(rs.getInt(9));
                cars.setSeatQty(rs.getInt(10));
                cars.setPrice(rs.getFloat(11));

                list.add(cars);
            }
            rs.close();
            pst.close();
            return list;
        } catch (Exception ex) {
            return null;
        } finally {
            DatabaseUtil.closeConnection(con);
        }
    }

    public boolean update(CarsDTO dto) {
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall(SQL_UPDATE);
            call.setInt(1, dto.getCarId());
            call.setString(2, dto.getCarName());
            call.setString(3, dto.getDetail());
            call.setInt(4, dto.getModelId());
            call.setInt(5, dto.getTypeId());
            if (dto.getAirConditional() == 1) {
                call.setBoolean(6, true);
            } else {
                call.setBoolean(6, false);
            }
            call.setInt(7, dto.getQuantity());
            call.setInt(8, dto.getSeatQty());
            call.setFloat(9, dto.getPrice());
            call.executeUpdate();
            return true;
        } catch (Exception ex) {
            return false;
        } finally {
            try {
                DatabaseUtil.closeConnection(con);
                call.close();
            } catch (SQLException ex) {
            }
        }
    }

    public boolean delete(int id) {
        try {
            con = DatabaseUtil.getConnection();
            con.setAutoCommit(false);
            call = con.prepareCall(SQL_DELETE);
            call.setInt(1, id);
            call.executeUpdate();
            con.commit();
            con.setAutoCommit(true);
            return true;
        } catch (Exception ex) {
            return false;
        } finally {
            try {
                DatabaseUtil.closeConnection(con);
                call.close();
            } catch (SQLException ex) {
            }
        }
    }

    public boolean delete(CarsDTO dto) {
        throw new UnsupportedOperationException("Not supported yet.");
    }
}
